from openai import OpenAI
import os
from vanna.openai import OpenAI_Chat
from vanna.chromadb import ChromaDB_VectorStore
from SimplePassword import SimplePassword

# os.environ['api_base'] = 'https://open.bigmodel.cn/api/paas/v4/'
os.environ['api_base'] = 'https://api.deepseek.com/'
# 使用自己的llm模型及所需配置
refresh_token = 'sk-xxx'

my_llm_base_url = 'https://api.deepseek.com/' #IP和端口号改为你自己的，我这里实际用的我的云服务器的地址

my_llm_name = 'deepseek-chat'

# 初始化客户端
client = OpenAI(
    api_key=refresh_token,
    base_url=my_llm_base_url,
    default_headers={"Authorization": f"Bearer {refresh_token}", "Content-Type": "application/json"}
)

class MyVanna(ChromaDB_VectorStore, OpenAI_Chat):
    def __init__(self, client=None, config=None):
        ChromaDB_VectorStore.__init__(self, config=config)
        OpenAI_Chat.__init__(self, client=client, config=config)

# 使用自定义的大模型及vanna提供的向量库
vn = MyVanna(client=client, config={"model": my_llm_name, })
vn.connect_to_postgres(host='192.168.2.162', dbname='JTBILL', user='postgres', password='Tianyuan@2020', port='5001')

vn.train(ddl="""
   CREATE TABLE if not exists prod_inst_acct_info (
	ID INT8 NOT NULL DEFAULT nextval('prod_inst_acct_info_id_seq'::regclass),
	prod_id INT8,
	prod_inst_id INT8 NOT NULL,
	bill_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
	cust_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
	belong_ym CHAR ( 6 ) COLLATE pg_catalog.DEFAULT,
	org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
	trust_flag CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
	trust_org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
	cust_id INT8,
	acct_id INT8,
	order_nbr VARCHAR ( 50 ) COLLATE pg_catalog.DEFAULT,
	acc_num VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
	contract_nbr VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
	rmb_all NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_all NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_port_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_main_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_main_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_local_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_local_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_port_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_main_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_local_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_loop_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_loop_b_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_inter_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	rmb_oppo_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_port_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_main_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_main_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_local_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_local_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_port_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_main_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_local_z_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_loop_a_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_loop_b_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_inter_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	us_oppo_amount NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	org_port_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	dis_port_a NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	org_main_a NUMERIC ( 19, 6 ) DEFAULT 0.000000,
	org_local_a NUMERIC ( 19, 6 ) DEFAULT 0.000000,
	dis_local_a NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	org_port_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	dis_port_z NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	org_main_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	org_local_z NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	dis_local_z NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	org_loop_a NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	org_loop_b NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	dis_loop NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	org_inter NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	dis_inter NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	org_oppo NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	cal_prod_inst_id INT8,
	bill_type CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
	cal_org_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
	notice_id VARCHAR ( 32 ) COLLATE pg_catalog.DEFAULT,
	us_pay_money NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	pay_money NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	pay_flag CHAR ( 1 ) COLLATE pg_catalog.DEFAULT DEFAULT '0' :: BPCHAR,
	taxes NUMERIC ( 19, 6 ),
	tax_rate NUMERIC ( 10, 6 ) DEFAULT 0.000000,
	belong_org INT8,
	
	
	NO_PAY_MONEY NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	NO_US_PAY_MONEY NUMERIC ( 19, 4 ) DEFAULT 0.0000,
	
	prov_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	latn_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	cust_region VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	create_org_id VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	cycle_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	fee_cycle VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	control_dep VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	begin_rent_cd VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	stop_rent_cd VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	industry_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	control_type VARCHAR ( 255 ) COLLATE pg_catalog.DEFAULT,
	account VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
	EXT_PROD_INST_ID VARCHAR ( 150 ) COLLATE pg_catalog.DEFAULT,
	PRIMARY KEY ( ID ) 
);

COMMENT ON COLUMN prod_inst_acct_info.ID IS '记录ID';
COMMENT ON COLUMN prod_inst_acct_info.prod_id IS '产品编码';
COMMENT ON COLUMN prod_inst_acct_info.prod_inst_id IS '产品实例ID';
COMMENT ON COLUMN prod_inst_acct_info.bill_ym IS '发生年月';
COMMENT ON COLUMN prod_inst_acct_info.cust_ym IS '客户应收年月';
COMMENT ON COLUMN prod_inst_acct_info.belong_ym IS '帐期计费年月';
COMMENT ON COLUMN prod_inst_acct_info.org_id IS '收款单位';
COMMENT ON COLUMN prod_inst_acct_info.trust_flag IS '是否代收费';
COMMENT ON COLUMN prod_inst_acct_info.trust_org_id IS '委托收款单位';
COMMENT ON COLUMN prod_inst_acct_info.cust_id IS '客户编号';
COMMENT ON COLUMN prod_inst_acct_info.acct_id IS '帐户编号';
COMMENT ON COLUMN prod_inst_acct_info.order_nbr IS '流水号';
COMMENT ON COLUMN prod_inst_acct_info.acc_num IS '电路代号';
COMMENT ON COLUMN prod_inst_acct_info.contract_nbr IS '合同号';
COMMENT ON COLUMN prod_inst_acct_info.rmb_all IS '应收金额';
COMMENT ON COLUMN prod_inst_acct_info.us_all IS '应收金额美元';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_a IS 'A端端口费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_a_amount IS 'A端端口费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_a IS 'A端代维费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_a_amount IS 'A端代维费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_a IS 'A端本地月租';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_a_amount IS 'A端本地月租调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_z IS 'Z端端口费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_port_z_amount IS 'Z端端口费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_z IS 'Z端代维费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_main_z_amount IS 'Z端代维费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_z IS 'Z端本地月租费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_local_z_amount IS 'Z端本地月租费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_a IS '国内A长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_a_amount IS '国内A长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_b IS '国内B长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_loop_b_amount IS '国内B长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_inter IS '国际长途费';
COMMENT ON COLUMN prod_inst_acct_info.rmb_inter_amount IS '国际长途费调整';
COMMENT ON COLUMN prod_inst_acct_info.rmb_oppo IS '国际对端金额';
COMMENT ON COLUMN prod_inst_acct_info.rmb_oppo_amount IS '国际对端金额调整';
COMMENT ON COLUMN prod_inst_acct_info.us_port_a IS 'A端端口费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_a_amount IS 'A端端口费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_a IS 'A端代维费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_a_amount IS 'A端代维费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_a IS 'A端本地月租美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_a_amount IS 'A端本地月租调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_z IS 'Z端端口费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_port_z_amount IS 'Z端端口费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_z IS 'Z端代维费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_main_z_amount IS 'Z端代维费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_z IS 'Z端本地月租费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_local_z_amount IS 'Z端本地月租费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_a IS '国内A长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_a_amount IS '国内A长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_b IS '国内B长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_loop_b_amount IS '国内B长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_inter IS '国际长途费美元';
COMMENT ON COLUMN prod_inst_acct_info.us_inter_amount IS '国际长途费调整美元';
COMMENT ON COLUMN prod_inst_acct_info.us_oppo IS '国际对端金额美元';
COMMENT ON COLUMN prod_inst_acct_info.us_oppo_amount IS '国际对端金额调整美元';
COMMENT ON COLUMN prod_inst_acct_info.org_port_a IS 'A端端口费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_port_a IS 'A端端口优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_main_a IS 'A端代维费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_local_a IS 'A端本地月租报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_local_a IS 'A端本地月租优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_port_z IS 'Z端端口费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_port_z IS 'Z端端口优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_main_z IS 'Z端代维费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_local_z IS 'Z端本地月租费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_local_z IS 'Z端本地优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_loop_a IS '国内A长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.org_loop_b IS '国内B长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_loop IS '国内长途优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_inter IS '国际长途费报价';
COMMENT ON COLUMN prod_inst_acct_info.dis_inter IS '国际长途优惠';
COMMENT ON COLUMN prod_inst_acct_info.org_oppo IS '国际对端金额报价';
COMMENT ON COLUMN prod_inst_acct_info.cal_prod_inst_id IS '当期计费资料ID';
COMMENT ON COLUMN prod_inst_acct_info.bill_type IS '收费类型(1--一次性费用，2-- 月租费用)';
COMMENT ON COLUMN prod_inst_acct_info.cal_org_id IS '作帐单位';
COMMENT ON COLUMN prod_inst_acct_info.notice_id IS '通知单ID';
COMMENT ON COLUMN prod_inst_acct_info.us_pay_money IS '销帐金额(US)';
COMMENT ON COLUMN prod_inst_acct_info.pay_money IS '销帐金额(RMB)';
COMMENT ON COLUMN prod_inst_acct_info.pay_flag IS '销帐标识';
COMMENT ON COLUMN prod_inst_acct_info.taxes IS '税金';
COMMENT ON COLUMN prod_inst_acct_info.tax_rate IS '税率，ICT时显示NULL值';
COMMENT ON COLUMN prod_inst_acct_info.belong_org IS '部门ID';

-- 新加的扩展字段
COMMENT ON COLUMN prod_inst_acct_info.NO_US_PAY_MONEY IS '未销账金额美元';
COMMENT ON COLUMN prod_inst_acct_info.NO_PAY_MONEY IS '未销账金额';



COMMENT ON COLUMN prod_inst_acct_info.latn_id IS '本地网代码';
COMMENT ON COLUMN prod_inst_acct_info.prov_id IS '省份代码';
COMMENT ON COLUMN prod_inst_acct_info.cust_region IS '客户营销责任地';
COMMENT ON COLUMN prod_inst_acct_info.create_org_id IS '发起局编码';
COMMENT ON COLUMN prod_inst_acct_info.cycle_type IS '收费周期';
COMMENT ON COLUMN prod_inst_acct_info.fee_cycle IS '费用周期';
COMMENT ON COLUMN prod_inst_acct_info.control_dep IS '管控部门';
COMMENT ON COLUMN prod_inst_acct_info.begin_rent_cd IS '起租系数';
COMMENT ON COLUMN prod_inst_acct_info.stop_rent_cd IS '退租系数';
COMMENT ON COLUMN prod_inst_acct_info.industry_type IS '行业类型';
COMMENT ON COLUMN prod_inst_acct_info.control_type IS '直管类型';
COMMENT ON COLUMN prod_inst_acct_info.account IS '接入号';
COMMENT ON COLUMN prod_inst_acct_info.EXT_PROD_INST_ID IS 'CRM实例ID';

""")



vn.train(ddl="""CREATE TABLE if not exists  idc_province_report (
    id int8 NOT NULL DEFAULT nextval('idc_province_report_id_seq'::regclass), -- 主键 ID, 使用序列实现自增
    province VARCHAR(255),                          -- 省份名称
    province_code text COLLATE pg_catalog.default,      -- 省份编码
    total_rack_count INT,                           -- 总机架数量
    total_bandwidth_count INT,                      -- 总带宽数量
    dynamic_traffic NUMERIC(18,2),                   -- 动态流量
    fixed_traffic NUMERIC(18,2),                     -- 固定流量
    total_revenue NUMERIC(18,2),                     -- 权责总收入
    managed_host_total_revenue NUMERIC(18,2),        -- 主机托管权责总收入
    idc_bandwidth_total_revenue NUMERIC(18,2),       -- IDC带宽权责总收入
    other_products_total_revenue NUMERIC(18,2),      -- 其他产品权责总收入
    one_time_fee NUMERIC(18,2),                      -- 一次性费用
    rack_slot_count INT,                             -- 机位数量
    rack_slot_revenue NUMERIC(18,2),                 -- 机位收入
    rack_count INT,                                  -- 机架数量
    rack_revenue NUMERIC(18,2),                      -- 机架收入
    power_count INT,                                 -- 电力数量
    power_revenue NUMERIC(18,2),                     -- 电力收入
    auxiliary_area_count INT,                        -- 工作附属区数量
    auxiliary_area_revenue NUMERIC(18,2),            -- 工作附属区收入
    machine_room_count INT,                          -- 机房数量
    machine_room_revenue NUMERIC(18,2),              -- 机房收入
    bgp_dynamic_traffic NUMERIC(18,2),               -- BGP动态流量
    bgp_dynamic_revenue NUMERIC(18,2),               -- BGP动态收入
    bgp_fixed_traffic NUMERIC(18,2),                 -- BGP固定流量
    bgp_fixed_revenue NUMERIC(18,2),                 -- BGP固定收入
    static_protocol_dynamic_traffic NUMERIC(18,2),  -- 静态协议动态流量
    static_protocol_dynamic_revenue NUMERIC(18,2),  -- 静态协议动态收入
    static_protocol_fixed_traffic NUMERIC(18,2),    -- 静态协议固定流量
    static_protocol_fixed_revenue NUMERIC(18,2),    -- 静态协议固定收入
    shared_port_traffic NUMERIC(18,2),               -- 共享端口流量
    shared_port_revenue NUMERIC(18,2),               -- 共享端口收入
    ip_broadcast_and_bgp_activation_revenue NUMERIC(18,2),  -- IP地址广播及BGP开通收入
    ip_rental_revenue NUMERIC(18,2),                 -- IP出租收入
    idc_value_added_service_revenue NUMERIC(18,2),   -- IDC增值业务收入
    trust_flag CHAR(1),                               -- 信任标志 (0: 非委托, 1: 委托),
	bill_ym text COLLATE pg_catalog.default, -- 计费年月
	
	latn_id varchar(32) COLLATE pg_catalog.default, --本地网代码
    prov_id varchar(32) COLLATE pg_catalog.default, -- 省份代码
	batch_num_ym text COLLATE pg_catalog.default, -- 批次年月便于数据清理
	
    PRIMARY KEY (id)                                 -- 设置主键
);

-- Add comments for each column
COMMENT ON COLUMN idc_province_report.province IS '省份名称';
COMMENT ON COLUMN idc_province_report.province_code IS '省份编码';
COMMENT ON COLUMN idc_province_report.total_rack_count IS '总机架数量';
COMMENT ON COLUMN idc_province_report.total_bandwidth_count IS '总带宽数量';
COMMENT ON COLUMN idc_province_report.dynamic_traffic IS '动态流量';
COMMENT ON COLUMN idc_province_report.fixed_traffic IS '固定流量';
COMMENT ON COLUMN idc_province_report.total_revenue IS '权责总收入';
COMMENT ON COLUMN idc_province_report.managed_host_total_revenue IS '主机托管权责总收入';
COMMENT ON COLUMN idc_province_report.idc_bandwidth_total_revenue IS 'IDC带宽权责总收入';
COMMENT ON COLUMN idc_province_report.other_products_total_revenue IS '其他产品权责总收入';
COMMENT ON COLUMN idc_province_report.one_time_fee IS '一次性费用';
COMMENT ON COLUMN idc_province_report.rack_slot_count IS '机位数量';
COMMENT ON COLUMN idc_province_report.rack_slot_revenue IS '机位收入';
COMMENT ON COLUMN idc_province_report.rack_count IS '机架数量';
COMMENT ON COLUMN idc_province_report.rack_revenue IS '机架收入';
COMMENT ON COLUMN idc_province_report.power_count IS '电力数量';
COMMENT ON COLUMN idc_province_report.power_revenue IS '电力收入';
COMMENT ON COLUMN idc_province_report.auxiliary_area_count IS '工作附属区数量';
COMMENT ON COLUMN idc_province_report.auxiliary_area_revenue IS '工作附属区收入';
COMMENT ON COLUMN idc_province_report.machine_room_count IS '机房数量';
COMMENT ON COLUMN idc_province_report.machine_room_revenue IS '机房收入';
COMMENT ON COLUMN idc_province_report.bgp_dynamic_traffic IS 'BGP动态流量';
COMMENT ON COLUMN idc_province_report.bgp_dynamic_revenue IS 'BGP动态收入';
COMMENT ON COLUMN idc_province_report.bgp_fixed_traffic IS 'BGP固定流量';
COMMENT ON COLUMN idc_province_report.bgp_fixed_revenue IS 'BGP固定收入';
COMMENT ON COLUMN idc_province_report.static_protocol_dynamic_traffic IS '静态协议动态流量';
COMMENT ON COLUMN idc_province_report.static_protocol_dynamic_revenue IS '静态协议动态收入';
COMMENT ON COLUMN idc_province_report.static_protocol_fixed_traffic IS '静态协议固定流量';
COMMENT ON COLUMN idc_province_report.static_protocol_fixed_revenue IS '静态协议固定收入';
COMMENT ON COLUMN idc_province_report.shared_port_traffic IS '共享端口流量';
COMMENT ON COLUMN idc_province_report.shared_port_revenue IS '共享端口收入';
COMMENT ON COLUMN idc_province_report.ip_broadcast_and_bgp_activation_revenue IS 'IP地址广播及BGP开通收入';
COMMENT ON COLUMN idc_province_report.ip_rental_revenue IS 'IP出租收入';
COMMENT ON COLUMN idc_province_report.idc_value_added_service_revenue IS 'IDC增值业务收入';
COMMENT ON COLUMN idc_province_report.trust_flag IS '信任标志 (0: 非信任, 1: 信任)';
COMMENT ON COLUMN idc_province_report.bill_ym IS '计费年月';
COMMENT ON COLUMN idc_province_report.batch_num_ym IS '批次年月便于数据清理';

COMMENT ON COLUMN idc_province_report.latn_id IS '本地网代码';
COMMENT ON COLUMN idc_province_report.prov_id IS '省份代码';
""")

vn.train(question="全国2024年8月税金最多的两个省份是？", sql="SELECT PROV_ID, SUM(TAXES) AS `T2024年8月税金` FROM `prod_inst_acct_info` WHERE CUST_YM = '202408' GROUP BY PROV_ID ORDER BY `T2024年8月税金` DESC LIMIT 2")
vn.train(question="教育行业2024年3月A端端口费是多少？", sql="SELECT SUM(RMB_PORT_A) AS `T2024年3月A端端口费` FROM   `prod_inst_acct_info` WHERE   CUST_YM = '202403'AND INDUSTRY_TYPE = 'CT13'")
vn.train(question="湖北省2024年6月总机架数量环比是多少？", sql="SELECT tmp.m_data, tmp.pre_m_data, ( ( tmp.m_data - tmp.pre_m_data ) / tmp.m_data ) * 100 AS  `环比`  FROM ( SELECT ( SELECT SUM ( COALESCE ( TOTAL_RACK_COUNT, 0 ) ) AS pre_m_data FROM idc_province_report WHERE BILL_YM = '202405' AND PROV_ID = '8420000' ) AS pre_m_data,  ( SELECT SUM ( TOTAL_RACK_COUNT ) AS m_data FROM idc_province_report WHERE BILL_YM = '202406' AND PROV_ID = '8420000' ) AS m_data    ) tmp")
vn.train(question="湖北省2024年8月总机架数量是多少？", sql=" select SUM ( TOTAL_RACK_COUNT ) as `机架数量`, PROV_ID  as `省份代码` , bill_ym as `账期` from idc_province_report where  bill_ym = '202408'  and PROV_ID = '8420000' GROUP BY PROV_ID, bill_ym")

from vanna.flask import VannaFlaskApp
app = VannaFlaskApp(vn,
                    # auth=SimplePassword(users=[{"email": "admin@example.com", "password": "password"}]),
                    logo="/images/logo2.png",
                    allow_llm_to_see_data=True,
                    summarization=True,
                    title="迪科chatbi",
                    subtitle="sql 生成器")
app.run(port = 6060, host = '0.0.0.0' , debug = True)